clear all

********************************************************************************



//Load data
////////////////////////////////////////////////////////////////////////////////

local k = 0
global series "114A 114B"
foreach s in $series "115_Year 1890" "115_Year 1891" "115_Year 1892" "116_Year 1893" "116_Year 1894" "116_Year 1895" "117_Year 1896" "117_Year 1897" "117_Year 1898" "118_Year 1899" {
local k = `k'+1
display in red "************************ Series `s' (`k')************************ "

import excel using "./data sheets/DATA_Series `s'.xlsx", clear sheet("Pages 1 - 2; 3") cellrange(a3) 


//Pages 1-2

***Mine info
{
rename A series
rename B year
rename C arrondissement
rename D ordernr
rename E mine_name
rename F mine_community
rename G concession_date
rename H concession_ha
rename I operations_active
rename J operations_reserve
rename K operations_construction
rename L vein_n
rename M avgvein_thickness

drop if missing(ordernr)
}

***Capital
{
rename N k_extraction_v_n
rename O k_extraction_m_n
rename P k_extraction_t_n
rename Q k_extraction_hp
rename R k_extraction_hp2
rename S k_extraction_hn
rename T k_excavation_n
rename U k_excavation_hp
rename V k_ventilation_n
rename W k_ventilation_hp
rename X k_other_n
rename Y k_other_hp
rename Z galleries_transport_n
rename AA galleries_water_n
rename AB horses_underground_n
rename AC horses_surface_n
}

***Labor
{
rename AD l_under_total_n
rename AE l_under_total_w
rename AF l_surface_total_n
rename AG l_surface_total_w
rename AH l_total_n
rename AI l_total_w
}


***Finances/Production
{
rename AJ avgprod_pit
rename AK avgprod_mine
rename AL avgdays_pit
rename AM avgdays_mine
rename AN expenses_std_l
rename AO expenses_std_other
rename AP expenses_std_total
rename AQ prixderevient_std_t
rename AR expenses_spc_l
rename AS expenses_spc_other
rename AT expenses_spc_total
rename AU expenses_l
rename AV expenses_other
rename AW expenses_total
rename AX prixderevient_t
}


//Page 3
drop AY


***Finances/Production (2)
rename AZ q_dry_t
rename BA q_dry_p
rename BB q_leans_t
rename BC q_leans_p
rename BD q_leanl_t
rename BE q_leanl_p
rename BF q_halfl_t
rename BG q_halfl_p
rename BH q_fatty_t
rename BI q_fatty_p

rename BJ q_total_t
rename BK q_total_pq
rename BL q_total_p
rename BM balance_gain
rename BN balance_loss
rename BO observations

capture rename BP consumption_t
capture rename BQ consumption_pq
capture rename BR consumption_stock



//String variables
////////////////////////////////////////////////////////////////////////////////
tostring series, replace
tostring concession_date, replace
tostring avgvein_thickness, replace
tostring l_*_w, replace
tostring avgprod_pit, replace

tempfile data`k'
save `data`k'', replace
}



//Merge series
////////////////////////////////////////////////////////////////////////////////


use `data1', clear
foreach t of numlist 2/`k' {
display in red "** `t' **"
append using `data`t''
}

replace series = "114" if series=="114A"|series=="114B"
destring series, replace


//Resolve multiple figures in one cell
////////////////////////////////////////////////////////////////////////////////


//Variable "avgprod_pit"
gen avgprod_pit_1 = trim(substr(avgprod_pit,1,strpos(avgprod_pit,",")-1)) if strpos(avgprod_pit,",")>0
gen avgprod_pit_2 = trim(substr(avgprod_pit,strpos(avgprod_pit,",")+1,.)) if strpos(avgprod_pit,",")>0
replace avgprod_pit_1 = avgprod_pit if strpos(avgprod_pit,",")==0
replace avgprod_pit_2 = avgprod_pit if strpos(avgprod_pit,",")==0
gen avgprod_pit_2_1 = trim(substr(avgprod_pit_2,1,strpos(avgprod_pit_2,",")-1)) if strpos(avgprod_pit_2,",")>0  //If there are 3 numbers (note: split command would be more succinct)
gen avgprod_pit_2_2 = trim(substr(avgprod_pit_2,strpos(avgprod_pit_2,",")+1,.)) if strpos(avgprod_pit_2,",")>0

rename avgprod_pit avgprod_pit_org
destring avgprod_pit_1 avgprod_pit_2, replace force
destring avgprod_pit_2_1 avgprod_pit_2_2, replace
gen avgprod_pit = (avgprod_pit_1+avgprod_pit_2)/2
replace avgprod_pit = (avgprod_pit_1 + avgprod_pit_2_1 + avgprod_pit_2_2)/3 if !missing(avgprod_pit_2_1)
destring avgprod_pit, replace
order avgprod_pit, before(avgprod_pit_org)
drop avgprod_pit_1 avgprod_pit_2 avgprod_pit_2_1 avgprod_pit_2_2

//Multiple types of wages
global varlist "l_under_total_w l_surface_total_w l_total_w"
foreach var in $varlist {
replace `var' = subinstr(`var', "q?", "qx", .)
replace `var' = subinstr(`var', "a?", "ax", .)
split `var', p(",")
local N = r(nvars)

rename `var' `var'_org

global term "qb qn ab an qx ax"
foreach t in $term {
gen `var'_`t' = ""
foreach k of numlist 1/`N' {
replace `var'_`t' = `var'`k' if strpos(`var'`k', "`t'") > 0
}
replace `var'_`t' = trim(subinstr(`var'_`t', "`t'","",.))
destring `var'_`t', replace
}

****Organize
destring `var'_org , gen(`var') force

replace `var' = `var'_qx if missing(`var') & !missing(`var'_qx)		
rename `var'_ax `var'_y 

rename `var'_qb `var'_gross
rename `var'_qn `var'_net
rename `var'_ab `var'_grossy
rename `var'_an `var'_nety

drop `var'1-`var'`N' `var'_qx
order `var' `var'_gross `var'_grossy `var'_net `var'_nety, after(`var'_org)
}


////////////////////////////////////////////////////////////////////////////////
save data_114-118.dta, replace
////////////////////////////////////////////////////////////////////////////////
exit





